* new apartment buildings treatment - with electoral data
********************************************************************************
* prelims
********************************************************************************
{
clear all
set more off
cap log close
}
********************************************************************************
* prepare data
********************************************************************************
{
* load ineq var census tract
use "$data/int/Ineq_08_900_CUSEC.dta", clear

* province identifier to merge
g CPRO = "08"

* add BARRI
merge m:m CPRO CMUN CDIS CSEC using "$data/int/catastro_plot_chars.dta", keepusing(BARRI)
drop if _merge==2
drop _merge

duplicates drop CPRO CMUN CDIS CSEC Year, force

* some CUSEC switch BARRI
bys CUSEC: egen x = mode(BARRI), minmode
replace BARRI = x
drop x

* add LNG
merge 1:1 CUSEC Year using "$data/int/LNG_08_900_CUSEC.dta", keepusing(VLNG*_wmean)
drop if _merge==2
drop _merge

* VLNG in 2015
g t_VLNG350_wmean_2015 = VLNG350_wmean if Year == 2015
bys CUSEC: egen VLNG350_wmean_2015 = mean(t_VLNG350_wmean_2015)
drop t_VLNG350_wmean_2015

* add changes tract level
merge 1:1 CUSEC Year using "$data/int/IneqChng_08_900_CUSEC.dta"
drop if _merge==2
drop _merge
	
* add new apartment events
merge 1:1 CMUN CDIS CSEC Year using "$data/int/CSEC_Dist_NewApt.dta", keepusing(TREAT1000 EVENT EvTract1 Dist1 EvTract2 Dist2)
drop if _merge==2
drop _merge

* add event charactersitics (CENSUS TRACT)
replace EvTract1 = CUSEC if EVENT == 1
replace Dist1 = 0 if EVENT == 1
rename CUSEC t_CUSEC
rename EvTract1 CUSEC

* add census tract characteristics
local evvars2 NewAptBldings NewApts 
merge m:1 CMUN CDIS CSEC Year using "$data/int/catastro_blng_expan.dta", keepusing(`evvars2')
drop if _merge==2
drop _merge
	
* no events = no new apts
replace NewApts = 0 if NewApts == .
replace NewAptBldings = 0 if NewAptBldings == .
	
* rename back
rename CUSEC EvTract1
rename t_CUSEC CUSEC

* add plot characteristics in CENSUS TRACT - in 2015
rename Year year
g Year = 2015

#d
local AddVar 
	PropSqmCSEC_mean PropSqmCSEC_med MedYearConstCSEC MeanYearConstCSEC MedNFloorsCSEC MeanNFloorsCSEC
	NAptsCSEC MedNAptsCSEC MeanNAptsCSEC
	MedQualCSEC MedAptSizeCSEC
	AreaSqm ShrSqmVCSEC ShrSqmGCCSEC ShrSqmGHCSEC ShrSqmGRCSEC ShrSqmACSEC ShrSqmCCSEC ShrSqmECSEC ShrSqmOCSEC ShrSqmPCSEC ShrSqmRCSEC
	;
#d cr

* add other info
merge m:1 CPRO CMUN CDIS CSEC Year using "$data/int/catastro_csec_chars.dta", keepusing(`AddVar')
drop if _merge==2
drop _merge

* rename
foreach var in `AddVar' {
	rename `var' `var'_2015
}

* qual scaled in tract
foreach mom in Med {

	g `mom'QualCSECScaled_2015 = (`mom'QualCSEC_2015 * -1) + 10
	la var `mom'QualCSECScaled_2015 "(`mom') Qual Construction in CSEC (2015)"
	drop `mom'QualCSEC_2015

}

drop Year
rename year Year

* add CATASTRO characteristics in CENSUS TRACT in year
#d
local AddVar 
	PropSqmCSEC_mean PropSqmCSEC_med MedYearConstCSEC MeanYearConstCSEC MedNFloorsCSEC MeanNFloorsCSEC
	NAptsCSEC MedNAptsCSEC MeanNAptsCSEC
	MeanPropYearCSEC MedPropYearCSEC
	MinQualCSEC MedQualCSEC
	MedAptSizeCSEC
	AreaSqm ShrSqmVCSEC ShrSqmGCCSEC ShrSqmGHCSEC ShrSqmGRCSEC ShrSqmACSEC ShrSqmCCSEC ShrSqmECSEC ShrSqmOCSEC ShrSqmPCSEC ShrSqmRCSEC
	;
#d cr
* add
merge 1:1 CPRO CMUN CDIS CSEC Year using "$data/int/catastro_csec_chars.dta", keepusing(`AddVar')
drop if _merge==2
drop _merge

* qual scaled in tract
foreach mom in Med {

	g `mom'QualCSECScaled = (`mom'QualCSEC * -1) + 10
	la var `mom'QualCSECScaled "(`mom') Qual Construction in CSEC"
	drop `mom'QualCSEC

}

* building age
g MeanPropAgeCSEC = 2020 - MeanPropYearCSEC 
g MeanConstAgeCSEC = 2020 - MeanYearConstCSEC
la var MeanPropAgeCSEC "Mean Prop Age in CSEC"
la var MeanConstAgeCSEC "Mean Building Age in CSEC"


* add 2011 census info
global censusvars married tot_pop age_16to64 age_more64 tertiary_educ h_rent tot_house
merge m:1 CPRO CMUN CDIS CSEC using "$data/int/census2011_csec.dta", keepusing($censusvars)
drop if _merge==2

* replacements
replace h_rent = 0 if h_rent == . & _merge!=1
replace tot_house = 0 if tot_house == . & _merge!=1 
replace tertiary_educ = 0 if tertiary_educ == . & _merge!=1 
replace age_more64 = 0 if age_more64 == . & _merge!=1 
g shrrentals = h_rent/tot_house

drop _merge

* share university
g ShrUniversity = tertiary_educ / tot_pop
la var ShrUniversity "Share Pop with Tertitary Education"

* share married
g ShrMarried = married / tot_pop
la var ShrMarried "Share Married"

* share age64+
g ShrAge64Plus = age_more64 / tot_pop
la var ShrAge64Plus "Share Pop Age 64+"

* add income from ATLAS
* prepare
rename Year year	
g Year = 2017
local varsatlas MedianHHInc IncSourceUI IncSourceOtherSub
* merge
merge m:1 CPRO CDIS CSEC CMUN Year using  "$data/int/ine_atlas_renta_csec.dta", keepusing(`varsatlas')
drop if _merge==2
drop _merge

* log median HH income
g lnMedianHHInc = ln(MedianHHInc)
la var lnMedianHHInc "log Median HH Inc (2017)"

* share of households whose main source of income is UI or other subsidies
g ShrHHUISub = (IncSourceUI + IncSourceOtherSub) / 100
g ShrHHUI = (IncSourceUI) / 100

* drop year var to merge
drop Year

* rentals in CSEC
local varsrent BI_ALVHEPCO_TVC BI_ALVHEPCO_TVU	
merge 1:1 year CPRO CMUN CDIS CSEC using "$data/int/rental_price_csec.dta", keepusing(`varsrent')
drop if _merge==2
drop _merge

rename year Year

replace BI_ALVHEPCO_TVU = 0 if BI_ALVHEPCO_TVU==. & (Year >=2015 & Year <= 2018)
replace BI_ALVHEPCO_TVC = 0 if BI_ALVHEPCO_TVC==. & (Year >=2015 & Year <= 2018)

* total rentals
g AptRentals = BI_ALVHEPCO_TVU + BI_ALVHEPCO_TVC
la var AptRentals "Apartment Rentals"

* share in the tract
g ShrRentals = AptRentals/NAptsCSEC
la var ShrRentals "Share of Apartments for Rent in the tract"
replace ShrRentals = shrrentals if Year == 2011

* from 2011 census
replace AptRentals = h_rent if Year == 2011
drop h_rent

* interpolate 2011-2015
bys CPRO CMUN CDIS CSEC: ipolate ShrRentals Year, g(ShrRentals_ipol) epolate

g x = ShrRentals if Year == 2011
bys CPRO CMUN CDIS CSEC: egen meanx = mean(x)
replace ShrRentals_ipol = meanx if Year < 2011
drop x meanx

g x = ShrRentals if Year == 2018
bys CPRO CMUN CDIS CSEC: egen meanx = mean(x)
replace ShrRentals_ipol = meanx if Year >= 2019
drop x meanx

drop ShrRentals shrrentals tot_house
rename ShrRentals_ipol ShrRentals

* add demographics from INE registry
g gender = "as"
rename Year year

* variables to generate mean age
* prepare
#d;
	local popagevars pop_0004 pop_0509 pop_1014 pop_1519 pop_2024 pop_2529 pop_3034 
	pop_3539 pop_4044 pop_4549 pop_5054 pop_5569 pop_6064 pop_6569 pop_7074 
	pop_7579 pop_8084 pop_8589 pop_9094 pop_9599 pop_100
	;
#d cr	
local varstokeep pop foreign esp_nationality foreign_nnt

replace year = 2004 if year < 2004

* merge
merge m:m year gender CDIS CSEC CPRO CMUN using "$data/int/reg_csec.dta", keepusing(`varstokeep' `popagevars')
drop if _merge==2
drop _merge

foreach var in `popagevars' {
	replace `var' = 0 if `var' == .
}

* mean age
#d;
	g MeanAge = 2 * (pop_0004/pop) + 7 * (pop_0509/pop) + 12 * (pop_1014/pop) +
	17 * (pop_1519/pop) + 22 * (pop_2024/pop) + 27 * (pop_2529/pop) +
	32 * (pop_3034/pop) + 37 * (pop_3539/pop) + 42 * (pop_4044/pop) +
	47 * (pop_4549/pop) + 52 * (pop_5054/pop) + 57 * (pop_5569/pop) +
	62 * (pop_6064/pop) + 67 * (pop_6569/pop) + 72 * (pop_7074/pop) +
	77 * (pop_7579/pop) + 82 * (pop_8084/pop) +  87 * (pop_8589/pop) +
	92 * (pop_9094/pop) + 97 * (pop_9599/pop) + 100 * (pop_100/pop)
	;
#d cr
la var MeanAge "Mean Age"
drop `popagevars'

* mean age in 2015
g t_MeanAge_2015 = MeanAge if year == 2015
bys CDIS CSEC CPRO CMUN: egen MeanAge_2015 = mean(t_MeanAge_2015)
drop t_MeanAge_2015

* shr female
rename pop t_pop
replace gender = "fe"
* merge
merge m:m year gender CDIS CSEC CPRO CMUN using "$data/int/reg_csec.dta", keepusing(pop)
drop if _m==2
drop _m
drop gender
* now generate share female
g ShrFemale = pop / t_pop
la var ShrFemale "Share Female"
replace ShrFemale = 0.5 if ShrFemale > 1 & ShrFemale!=.

* share female in 2015
g t_ShrFemale_2015 = ShrFemale if year == 2015
bys CDIS CSEC CPRO CMUN: egen ShrFemale_2015 = mean(t_ShrFemale_2015)
drop t_ShrFemale_2015

drop pop
rename t_pop pop

* share foreign and share foreign in 2015
g shrforeign = foreign_nnt / pop
g t_shrforeign_2015 = shrforeign if year == 2015
bys CDIS CSEC CPRO CMUN: egen shrforeign_2015 = mean(t_shrforeign_2015)
drop t_shrforeign_2015

* drop year variable
rename year Year

* generate shar of vars
foreach var in `varstokeep' {

	g `var'shr = `var' / pop

}
drop popshr
	
* pop density
g PopDensity = pop / AreaSqm 	

* population density in 2015
g t_PopDensity_2015 = PopDensity if Year == 2015
bys CDIS CSEC CPRO CMUN: egen PopDensity_2015 = mean(t_PopDensity_2015)
drop t_PopDensity_2015

* adjust shr of female
replace ShrFemale = 0.5 if ShrFemale==. & PopDensity!=.

* district FE
egen CDISFE = group(CDIS)

* make CUSEC integer
egen cusec = group(CUSEC)

* declare panel
xtset cusec Year
sort cusec Year

* generate treatment variable sensitive to distance
replace TREAT1000 = 1 if EVENT==1
* no event in tract
g NoEvInTract_1Yr = (EVENT==0 & l1.EVENT==0)
g NoEvInTract_2Yr = (EVENT==0 & l1.EVENT==0 & l2.EVENT==0)
g NoEvInTract_3Yr = (EVENT==0 & l1.EVENT==0 & l2.EVENT==0 & l3.EVENT==0)
g NoEvInTract_4Yr = (EVENT==0 & l1.EVENT==0 & l2.EVENT==0 & l3.EVENT==0 & l4.EVENT==0)

* minimum event distance in previous years
forval i =1(1)4{
	g Dist1_`i' = l`i'.Dist1
}

g MinDist_0Yr = (Dist1_1)
egen MinDist_1Yr = rowmin(Dist1 Dist1_1)
egen MinDist_2Yr = rowmin(Dist1 Dist1_1 Dist1_2)
egen MinDist_3Yr = rowmin(Dist1 Dist1_1 Dist1_2 Dist1_3)
egen MinDist_4Yr = rowmin(Dist1 Dist1_1 Dist1_2 Dist1_3 Dist1_4)

drop Dist1_*

* treatment vars
foreach dist in 1000 750 500 350 200 100 {
	
	* generate treatment variable for relevant distance
	cap g TREAT`dist' = (TREAT1000==1)
	replace TREAT`dist' = 0 if Dist1 > `dist'
	
	* generate lagged treatment
	g T`dist'_0Yrs = (TREAT`dist'==1)
	g T`dist'_1Yrs = (TREAT`dist'==1 | l1.TREAT`dist'==1)
	g T`dist'_2Yrs = (TREAT`dist'==1 | l1.TREAT`dist'==1 | l2.TREAT`dist'==1)
	g T`dist'_3Yrs = (TREAT`dist'==1 | l1.TREAT`dist'==1 | l2.TREAT`dist'==1 | l3.TREAT`dist'==1)
	g T`dist'_4Yrs = (TREAT`dist'==1 | l1.TREAT`dist'==1 | l2.TREAT`dist'==1 | l3.TREAT`dist'==1 | l4.TREAT`dist'==1)
	
	* generate lagged treatment - before election
	g T`dist'_BefE_1Yrs = (l1.TREAT`dist'==1)
	g T`dist'_BefE_2Yrs = (l1.TREAT`dist'==1 | l2.TREAT`dist'==1)
	g T`dist'_BefE_3Yrs = (l1.TREAT`dist'==1 | l2.TREAT`dist'==1 | l3.TREAT`dist'==1)
	g T`dist'_BefE_4Yrs = (l1.TREAT`dist'==1 | l2.TREAT`dist'==1 | l3.TREAT`dist'==1 | l4.TREAT`dist'==1)

	* identify if no event in past two years
	g NoEv`dist'_1Yr = (l1.TREAT`dist'==0)
	g NoEv`dist'_2Yr = (l1.TREAT`dist'==0 & l2.TREAT`dist'==0 )
	g NoEv`dist'_3Yr = (l1.TREAT`dist'==0 & l2.TREAT`dist'==0 & l3.TREAT`dist'==0 )
	g NoEv`dist'_4Yr = (l1.TREAT`dist'==0 & l2.TREAT`dist'==0 & l3.TREAT`dist'==0  & l4.TREAT`dist'==0 )
	
	* identify if tract has an event
	g x`dist' = (Dist1 <= `dist')
	* new apartments to count
	g NewApts`dist' = NewApts * x`dist'
	* count
	g NewAptsEv`dist'_0Yrs = (NewApts`dist')
	g NewAptsEv`dist'_1Yrs = (NewApts`dist' + l1.NewApts`dist')
	g NewAptsEv`dist'_2Yrs = (NewApts`dist' + l1.NewApts`dist' + l2.NewApts`dist')
	g NewAptsEv`dist'_3Yrs = (NewApts`dist' + l1.NewApts`dist' + l2.NewApts`dist' + l3.NewApts`dist')
	g NewAptsEv`dist'_4Yrs = (NewApts`dist' + l1.NewApts`dist' + l2.NewApts`dist' + l3.NewApts`dist' + l4.NewApts`dist')
	
	* as a share of apartments in census tract
	g ShrNewApts`dist'_0Yrs = (NewApts`dist')/ NAptsCSEC
	g ShrNewApts`dist'_1Yrs = (NewApts`dist' + l1.NewApts`dist')/ NAptsCSEC
	g ShrNewApts`dist'_2Yrs = (NewApts`dist' + l1.NewApts`dist' + l2.NewApts`dist')/ NAptsCSEC
	g ShrNewApts`dist'_3Yrs = (NewApts`dist' + l1.NewApts`dist' + l2.NewApts`dist' + l3.NewApts`dist')/ NAptsCSEC
	g ShrNewApts`dist'_4Yrs = (NewApts`dist' + l1.NewApts`dist' + l2.NewApts`dist' + l3.NewApts`dist' + l4.NewApts`dist')/ NAptsCSEC

}
	
* lagged LNG variables
sort cusec Year
	
foreach var in VLNG200_wmean VLNG350_wmean VLNG500_wmean {
forval i = 1(1)4 {
	g l`i'_`var' = l`i'.`var'
	
}
	
* value in 2009
g t_l2009_`var' = `var' if Year==2009
bys cusec: egen l2009_`var' = mean(t_l2009_`var')
drop t_l2009_`var'
	
}

* add electoral data
g ElecYear = Year
merge 1:m CMUN CDIS CSEC ElecYear using "$data/int/elections_bcn.dta", keepusing(PctVotes* ElecMonth)
drop if _merge==2
drop _merge

g x = PctVotesLEFT if ElecYear==2015 
bys CMUN CPRO CDIS CSEC: egen PctVotesLEFT_2015NatE = mean(x)
drop x

* duplicate census tracts
duplicates drop CMUN CPRO CDIS CSEC ElecYear ElecMonth, force

* labels
la def BldngShock 1 "New Building Treatment" 0 "Controls"
la val T350_3Yrs BldngShock
la var T350_3Yrs "New Building Treatment"
la val T350_BefE_3Yrs BldngShock
la var T350_BefE_3Yrs "New Building Treatment"
la var ShrRentals "Share Rentals in Tract"

* distance to closest metro station
g SEC_CENS = CSEC
g DISTRICTE = CDIS

merge m:m SEC_CENS DISTRICTE using "$data/int/distance_stations_tract.dta", keepusing(METRO*)
drop if _merge == 2
drop _merge
duplicates drop CUSEC Year, force

qui lookfor METRO
g DistMetro = .
la var DistMetro "Distance to Closest Metro Station"
foreach var in `r(varlist)' {

replace DistMetro = `var' if `var' < DistMetro
drop `var'

}

* distance to closest school
merge m:m SEC_CENS DISTRICTE using "$data/int/distance_schools_tract.dta", keepusing(SCL*)
drop if _merge == 2
drop _merge
duplicates drop CUSEC Year, force

g DistSchool = .
la var DistSchool "Distance to Closest School"
qui lookfor SCL
foreach var in `r(varlist)' {

replace DistSchool = `var' if `var' < DistSchool
drop `var'

}

* distance to closest park
merge m:m SEC_CENS DISTRICTE using "$data/int/distance_parks_tract.dta", keepusing(PRK*)
drop if _merge == 2
drop _merge
duplicates drop CUSEC Year, force

g DistPark = .
la var DistPark "Distance to Closest Park"
qui lookfor PRK
foreach var in `r(varlist)' {

replace DistPark = `var' if `var' < DistPark
drop `var'

}

* distance to closest hospital
merge m:m SEC_CENS DISTRICTE using "$data/int/distance_hospitals_tract.dta", keepusing(HSP*)
drop if _merge == 2
drop _merge
duplicates drop CUSEC Year, force

g DistHosp = .
la var DistHosp "Distance to Closest Hospital"
qui lookfor HSP
foreach var in `r(varlist)' {

replace DistHosp = `var' if `var' < DistHosp
drop `var'

}

drop SEC_CENS DISTRICTE
	
* store
compress
save "$data/analysis_dataset_aggcsec.dta", replace

}
********************************************************************************
* closing
********************************************************************************
{
cap log close
clear
}
